﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using ReservationModule.Classes;


namespace ReservationModule
{
    public class DBManager
    {
        public static int UpdateRoom(Room r, Reservation re)
        {
            int rowsupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE TOP (@numberofrooms) Room SET RoomStatus=@roomstatus, ReservationID=@reservationid  WHERE RoomType=@roomtype AND RoomStatus=@roomstatus2";
                comm.Parameters.AddWithValue("@numberofrooms", re.NumberOfRooms1);
                comm.Parameters.AddWithValue("@roomstatus", r.RoomStatus1);
                comm.Parameters.AddWithValue("@roomtype", r.RoomType1);
                comm.Parameters.AddWithValue("@reservationid", re.ReservationID1);
                comm.Parameters.AddWithValue("@roomstatus2", "Vacant");
                rowsupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        }

        public static int UpdateCreditCard(CreditCard cc)
        {
            int rowsupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE CREDITCARD SET CreditCardNumber=@creditcardnumber, ExpiryDate=@expirydate, NameOfCardHolder=@nameofcardholder, CreditCardType=@creditcardtype  WHERE CustomerID=@customerid";
                comm.Parameters.AddWithValue("@creditcardnumber", cc.CreditCardNumber1);
                comm.Parameters.AddWithValue("@expirydate", cc.ExpiryDate1);
                comm.Parameters.AddWithValue("@nameofcardholder", cc.NameOfCardHolder1);
                comm.Parameters.AddWithValue("@creditcardtype", cc.CreditCardType1);
                comm.Parameters.AddWithValue("@customerid", cc.CustomerID1);
                rowsupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        }

        public static int UpdateCustomer(Customer cu)
        {
            int rowsupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE CUSTOMER SET NRIC=@nric, FirstName=@firstname, LastName=@lastname,PostalCode=@postalcode, City=@city, Country=@country, Nationality=@nationality, ContactNumber=@contactnumber, Address=@address,  Email=@email, Gender=@gender, DateOfBirth=@dateofbirth, AdditionalComment=@additionalcomment  WHERE CustomerID=@customerid";
                comm.Parameters.AddWithValue("@nric", cu.NRIC1);
                comm.Parameters.AddWithValue("@firstname", cu.FirstName1);
                comm.Parameters.AddWithValue("@lastname", cu.LastName1);
                comm.Parameters.AddWithValue("@postalcode", cu.PostalCode1);
                comm.Parameters.AddWithValue("@city", cu.City1);
                comm.Parameters.AddWithValue("@country", cu.Country1);
                comm.Parameters.AddWithValue("@nationality", cu.Nationality1);
                comm.Parameters.AddWithValue("@contactnumber", cu.ContactNumber1);
                comm.Parameters.AddWithValue("@address", cu.Address1);
                comm.Parameters.AddWithValue("@email", cu.Email1);
                comm.Parameters.AddWithValue("@gender", cu.Gender1);
                comm.Parameters.AddWithValue("@dateofbirth", cu.DateOfBirth1);
                comm.Parameters.AddWithValue("@additionalcomment", cu.AdditionalComment1);
                comm.Parameters.AddWithValue("@customerid", cu.CustomerID1);
                rowsupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        }

        public static int UpdateReservation(Reservation re)
        {
            int rowsupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE RESERVATION SET DesiredCheckInDate=@desiredcheckindate, DesiredCheckOutDate=@desiredcheckoutdate, NumberOfGuestAdults=@numberofguestadults, NumberOfGuestChildren=@numberofguestchildren, NumberOfNights=@numberofnights, ReservationStatus=@reservationstatus, AdditionalRemarks=@additionalremarks, NumberOfRooms=@numberofrooms  WHERE CustomerID=@customerid";
                comm.Parameters.AddWithValue("@desiredcheckindate", re.DesiredCheckInDate1);
                comm.Parameters.AddWithValue("@desiredcheckoutdate", re.DesiredCheckOutDate1);
                comm.Parameters.AddWithValue("@numberofguestadults", re.NumberOfGuestAdults1);
                comm.Parameters.AddWithValue("@numberofguestchildren", re.NumberOfGuestChildren1);
                comm.Parameters.AddWithValue("@numberofnights", re.NumberOfNights1);
                comm.Parameters.AddWithValue("@reservationstatus", re.ReservationStatus1);
                comm.Parameters.AddWithValue("@additionalremarks", re.AdditionalRemarks1);
                comm.Parameters.AddWithValue("@numberofrooms", re.NumberOfRooms1);
                comm.Parameters.AddWithValue("@customerid", re.CustomerID1);
                rowsupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        }

        public static int UpdateRoomStatus(Room r)
        {
            int rowsupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE ROOM SET RoomStatus=@roomstatus, ReservationID=@reservationid2 WHERE ReservationID=@reservationid";
                comm.Parameters.AddWithValue("@roomstatus", r.RoomStatus1);
                comm.Parameters.AddWithValue("@reservationid", r.ReservationID1);
                comm.Parameters.AddWithValue("@reservationid2", DBNull.Value);
                rowsupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        }

        public static int CreateCustomer(Customer cu)
        {
            int rows = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO CUSTOMER(NRIC, FirstName, LastName, PostalCode, City, Country, Nationality, ContactNumber, Address, Email, Gender, DateOfBirth, AdditionalComment)" +
                " VALUES (@nric, @firstname, @lastname, @postalcode, @city, @country, @nationality, @contactnumber, @address, @email, @gender, @dob, @additionalcomment)";

                comm.Parameters.AddWithValue("@nric", cu.NRIC1);
                comm.Parameters.AddWithValue("@firstname", cu.FirstName1);
                comm.Parameters.AddWithValue("@lastname", cu.LastName1);
                comm.Parameters.AddWithValue("@postalcode", cu.PostalCode1);
                comm.Parameters.AddWithValue("@city", cu.City1);
                comm.Parameters.AddWithValue("@country", cu.Country1);
                comm.Parameters.AddWithValue("@nationality", cu.Nationality1);
                comm.Parameters.AddWithValue("@contactnumber", cu.ContactNumber1);
                comm.Parameters.AddWithValue("@address", cu.Address1);
                comm.Parameters.AddWithValue("@email", cu.Email1);
                comm.Parameters.AddWithValue("@gender", cu.Gender1);
                comm.Parameters.AddWithValue("@dob", cu.DateOfBirth1);
                comm.Parameters.AddWithValue("@additionalcomment", cu.AdditionalComment1);
                rows = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rows;
        }

        public static int CreateCreditCard(CreditCard cc)
        {
            int rows = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO CreditCard(CreditCardNumber, ExpiryDate, NameOfCardHolder, CreditCardType, CustomerID)" +
                " VALUES (@creditcardnumber, @expirydate, @nameofcardholder, @creditcardtype, @customerid)";

                comm.Parameters.AddWithValue("@creditcardnumber", cc.CreditCardNumber1);
                comm.Parameters.AddWithValue("@expirydate", cc.ExpiryDate1);
                comm.Parameters.AddWithValue("@nameofcardholder", cc.NameOfCardHolder1);
                comm.Parameters.AddWithValue("@creditcardtype", cc.CreditCardType1);
                comm.Parameters.AddWithValue("@customerid", cc.CustomerID1);
                rows = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rows;
        }

        public static Customer GetCustomerByNRIC(string Nric)
        {
            SqlConnection conn = null;
            Customer cu = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString =
                    ConfigurationManager.ConnectionStrings["HMSDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM CUSTOMER WHERE NRIC=@nric";
                comm.Parameters.AddWithValue("@nric", Nric);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    cu = new Customer();
                    cu.CustomerID1 = (int)dr["CustomerID"];
                }
                dr.Close();

            }
            catch (SqlException e)
            {
                throw e;
            }

            return cu;
        }

        public static int CreateReservation(Reservation re)
        {
            int rows = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO RESERVATION(CustomerID, DesiredCheckInDate, DesiredCheckOutDate, NumberOfGuestAdults, NumberOfGuestChildren, NumberOfNights, ReservationStatus, AdditionalRemarks, NumberOfRooms)" +
                " VALUES (@customerid, @desiredcheckindate, @desiredcheckoutdate, @numberofguestadults, @numberofguestchildren, @numberofnights, @reservationstatus, @additionalremarks, @numberofrooms)";

                comm.Parameters.AddWithValue("@customerid", re.CustomerID1);
                comm.Parameters.AddWithValue("@desiredcheckindate", re.DesiredCheckInDate1);
                comm.Parameters.AddWithValue("@desiredcheckoutdate", re.DesiredCheckOutDate1);
                comm.Parameters.AddWithValue("@numberofguestadults", re.NumberOfGuestAdults1);
                comm.Parameters.AddWithValue("@numberofguestchildren", re.NumberOfGuestChildren1);
                comm.Parameters.AddWithValue("@numberofnights", re.NumberOfNights1);
                comm.Parameters.AddWithValue("@reservationstatus", re.ReservationStatus1);
                comm.Parameters.AddWithValue("@additionalremarks", re.AdditionalRemarks1);
                comm.Parameters.AddWithValue("@numberofrooms", re.NumberOfRooms1);
                rows = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rows;
        }

        public static Reservation GetReservationByCustomerID(int customerID)
        {
            SqlConnection conn = null;
            Reservation re = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString =
                    ConfigurationManager.ConnectionStrings["HMSDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM RESERVATION WHERE CustomerID=@customerid";
                comm.Parameters.AddWithValue("@customerid", customerID);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    re = new Reservation();
                    re.ReservationID1 = (int)dr["ReservationID"];
                    re.CustomerID1 = (int)dr["CustomerID"];
                    re.DesiredCheckInDate1 = (DateTime)dr["DesiredCheckInDate"];
                    re.DesiredCheckOutDate1 = (DateTime)dr["DesiredCheckOutDate"];
                    re.NumberOfGuestAdults1 = (int)dr["NumberOfGuestAdults"];
                    re.NumberOfGuestChildren1 = (int)dr["NumberOfGuestChildren"];
                    re.NumberOfNights1 = (int)dr["NumberOfNights"];
                    re.ReservationStatus1 = (string)dr["ReservationStatus"];
                    re.AdditionalRemarks1 = (string)dr["AdditionalRemarks"];
                    re.NumberOfRooms1 = (int)dr["NumberOfRooms"];
                }
                dr.Close();

            }
            catch (SqlException e)
            {
                throw e;
            }

            return re;
        }

        public static Customer GetCustomerByCustomerID(int customerID)
        {
            SqlConnection conn = null;
            Customer cu = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString =
                    ConfigurationManager.ConnectionStrings["HMSDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM CUSTOMER WHERE CustomerID=@customerid";
                comm.Parameters.AddWithValue("@customerid", customerID);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    cu = new Customer();
                    cu.CustomerID1 = (int)dr["CustomerID"];
                    cu.NRIC1 = (string)dr["NRIC"];
                    cu.FirstName1 = (string)dr["FirstName"];
                    cu.LastName1 = (string)dr["LastName"];
                    cu.PostalCode1 = (string)dr["PostalCode"];
                    cu.City1 = (string)dr["City"];
                    cu.Country1 = (string)dr["Country"];
                    cu.Nationality1 = (string)dr["Nationality"];
                    cu.ContactNumber1 = (string)dr["ContactNumber"];
                    cu.Address1 = (string)dr["Address"];
                    cu.Email1 = (string)dr["Email"];
                    cu.Gender1 = (string)dr["Gender"];
                    cu.AdditionalComment1 = (string)dr["AdditionalComment"];
                    cu.DateOfBirth1 = (DateTime)dr["DateOfBirth"];

                }
                dr.Close();

            }
            catch (SqlException e)
            {
                throw e;
            }

            return cu;
        }

        public static Room GetRoomByReservationID(int reservationID)
        {
            SqlConnection conn = null;
            Room r = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString =
                    ConfigurationManager.ConnectionStrings["HMSDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT TOP (1) * FROM ROOM WHERE ReservationID=@reservationid";
                comm.Parameters.AddWithValue("@reservationid", reservationID);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    r = new Room();
                    r.RoomType1 = (string)dr["RoomType"];
                    r.RoomRate1 = (decimal)dr["RoomRate"];

                }
                dr.Close();

            }
            catch (SqlException e)
            {
                throw e;
            }

            return r;
        }

        public static CreditCard GetCreditCardByCustomerID(int customerID)
        {
            SqlConnection conn = null;
            CreditCard cc = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString =
                    ConfigurationManager.ConnectionStrings["HMSDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM CreditCard WHERE CustomerID=@customerid";
                comm.Parameters.AddWithValue("@customerid", customerID);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    cc = new CreditCard();
                    cc.CreditCardNumber1 = (string)dr["CreditCardNumber"];
                    cc.ExpiryDate1 = (string)dr["ExpiryDate"];
                    cc.NameOfCardHolder1 = (string)dr["NameOfCardHolder"];
                    cc.CreditCardType1 = (string)dr["CreditCardType"];
                }
                dr.Close();

            }
            catch (SqlException e)
            {
                throw e;
            }

            return cc;
        }

        public static int DeleteReservation(int reservationID)
        {
            int rowsdeleted = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString =
                    ConfigurationManager.ConnectionStrings["HMSDBConnectionString"].ConnectionString;
                conn.Open();

                //prepare SQL command
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM RESERVATION WHERE ReservationID=@reservationid";

                comm.Parameters.AddWithValue("@reservationid", reservationID);

                //Execute the SQL command
                rowsdeleted = comm.ExecuteNonQuery();

                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsdeleted;
        }

        public static int DeleteCustomer(int customerID)
        {
            int rowsdeleted = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString =
                    ConfigurationManager.ConnectionStrings["HMSDBConnectionString"].ConnectionString;
                conn.Open();

                //prepare SQL command
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM CUSTOMER WHERE CustomerID=@customerid";

                comm.Parameters.AddWithValue("@customerid", customerID);

                //Execute the SQL command
                rowsdeleted = comm.ExecuteNonQuery();

                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsdeleted;
        }

        public static int DeleteCreditCard(int customerID)
        {
            int rowsdeleted = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString =
                    ConfigurationManager.ConnectionStrings["HMSDBConnectionString"].ConnectionString;
                conn.Open();

                //prepare SQL command
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM CREDITCARD WHERE CustomerID=@customerid";

                comm.Parameters.AddWithValue("@customerid", customerID);

                //Execute the SQL command
                rowsdeleted = comm.ExecuteNonQuery();

                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsdeleted;
        }

        public static int CreateStaff(Staff s)
        {
            int rows = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO STAFF(StaffNRIC, StaffName, StaffType, DateOfBirth, Nationality, ContactNumber, BankAccountNumber, DutyType)" +
                " VALUES (@staffnric, @staffname, @stafftype, @dateofbirth, @nationality, @contactnumber, @bankaccountnumber, @dutytype)";


                comm.Parameters.AddWithValue("@staffnric", s.StaffNRIC1);
                comm.Parameters.AddWithValue("@staffname", s.StaffName1);
                comm.Parameters.AddWithValue("@stafftype", s.StaffType1);
                comm.Parameters.AddWithValue("@dateofbirth", s.DateOfBirth1);
                comm.Parameters.AddWithValue("@nationality", s.Nationality1);
                comm.Parameters.AddWithValue("@contactnumber", s.ContactNumber1);
                comm.Parameters.AddWithValue("@bankaccountnumber", s.BankAccountNumber1);
                comm.Parameters.AddWithValue("@dutytype", s.DutyType1);
                rows = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                return rows=2;
            }
            return rows;
        }

        public static Staff GetStaffByStaffNRIC(string staffNRIC)
        {
            SqlConnection conn = null;
            Staff s = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString =
                    ConfigurationManager.ConnectionStrings["HMSDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM STAFF WHERE StaffNRIC=@staffnric";
                comm.Parameters.AddWithValue("@staffnric", staffNRIC);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    s = new Staff();
                    s.StaffNRIC1 = (string)dr["StaffNRIC"];
                    s.StaffName1 = (string)dr["StaffName"];
                    s.StaffType1 = (string)dr["StaffType"];
                    s.DateOfBirth1 = (DateTime)dr["DateOfBirth"];
                    s.Nationality1 = (string)dr["Nationality"];
                    s.ContactNumber1 = (string)dr["ContactNumber"];
                    s.BankAccountNumber1 = (string)dr["BankAccountNumber"];
                    s.DutyType1 = (string)dr["DutyType"];

                }
                dr.Close();

            }
            catch (SqlException e)
            {
                throw e;
            }

            return s;
        }

        public static int UpdateStaff(Staff s)
        {
            int rowsupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE STAFF SET StaffName=@staffname, StaffType=@stafftype, DateOfBirth=@dateofbirth, Nationality=@nationality, ContactNumber=@contactnumber, BankAccountNumber=@bankaccountnumber, DutyType=@dutytype WHERE StaffNRIC=@staffnric";
                comm.Parameters.AddWithValue("@staffnric", s.StaffNRIC1);
                comm.Parameters.AddWithValue("@staffname", s.StaffName1);
                comm.Parameters.AddWithValue("@stafftype", s.StaffType1);
                comm.Parameters.AddWithValue("@dateofbirth", s.DateOfBirth1);
                comm.Parameters.AddWithValue("@nationality", s.Nationality1);
                comm.Parameters.AddWithValue("@contactnumber", s.ContactNumber1);
                comm.Parameters.AddWithValue("@bankaccountnumber", s.BankAccountNumber1);
                comm.Parameters.AddWithValue("@dutytype", s.DutyType1);
                rowsupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        }

        public static int DeleteStaff(string staffNRIC)
        {
            int rowsdeleted = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString =
                    ConfigurationManager.ConnectionStrings["HMSDBConnectionString"].ConnectionString;
                conn.Open();

                //prepare SQL command
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM STAFF WHERE StaffNRIC=@staffnric";
                comm.Parameters.AddWithValue("@staffnric", staffNRIC);
                //Execute the SQL command
                rowsdeleted = comm.ExecuteNonQuery();

                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsdeleted;
        }

        public static int GetRoomAvailibilityByDates(DateTime checkinDate, DateTime checkoutDate, string roomtype)
        {
            SqlConnection conn = null;

            int count = 0;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString =
                    ConfigurationManager.ConnectionStrings["HMSDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                //comm.CommandText="SELECT COUNT(*) AS RETURNCOUNT FROM RESERVATION AS n INNER JOIN ROOM AS r ON n.ReservationID=r.ReservationID WHERE RoomType=@roomtype AND (@checkin NOT BETWEEN DesiredCheckInDate AND DesiredCheckOutDate) AND (@checkout NOT BETWEEN DesiredCheckInDate AND DesiredCheckOutDate)";
                comm.CommandText = "Select Count (*) FROM Reservation AS n INNER JOIN Room AS r ON n.ReservationID=r.ReservationID WHERE RoomType=@roomtype AND (@checkin NOT BETWEEN DesiredCheckInDate AND DesiredCheckOutDate) AND (@checkout NOT BETWEEN DesiredCheckInDate AND DesiredCheckOutDate)";
                comm.Parameters.AddWithValue("@checkin", checkinDate);
                comm.Parameters.AddWithValue("@checkout", checkoutDate);
                comm.Parameters.AddWithValue("@roomtype", roomtype);

                count = (int)comm.ExecuteScalar();
                if (count <= 0)
                    comm.CommandText = "Select Count(*) FROM Room WHERE RoomType=@roomtype AND ReservationID is null";
                count = (int)comm.ExecuteScalar();
            }
            catch (SqlException e)
            {
                throw e;
            }

            return count;
        }

        public static int CreatePaymentInvoice(PaymentInvoice inv)
        {
            int rows = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO PaymentInvoice(PaymentInvoiceID, CustomerID, ReservationID, CheckInDate, CheckOutDate, NumberOfNights, NumberOfRooms, NumberOfGuestAdults, NumberOfGuestChildren, PaymentMode, RoomCharges, Tax, Total, AdditionalRemarks)" +
                " VALUES (@paymentinvoiceid, @customerid, @reservationid, @checkindate, @checkoutdate, @numberofnights, @numberofrooms, @numberofguestadults, @numberofguestchildren, @paymentmode, @roomcharges, @tax, @total, @additionalremarks)";

                comm.Parameters.AddWithValue("@paymentinvoiceid", inv.PaymentInvoiceID1);
                comm.Parameters.AddWithValue("@customerid", inv.CustomerID1);
                comm.Parameters.AddWithValue("@reservationid", inv.ReservationID1);
                comm.Parameters.AddWithValue("@checkindate", inv.CheckInDate1);
                comm.Parameters.AddWithValue("@checkoutdate", inv.CheckOutDate1);
                comm.Parameters.AddWithValue("@numberofnights", inv.NumberOfNights1);
                comm.Parameters.AddWithValue("@numberofrooms", inv.NumberOfRooms1);
                comm.Parameters.AddWithValue("@numberofguestadults", inv.NumberOfGuestAdults1);
                comm.Parameters.AddWithValue("@numberofguestchildren", inv.NumberOfGuestChildren1);
                comm.Parameters.AddWithValue("@paymentmode", inv.PaymentMode1);
                comm.Parameters.AddWithValue("@roomcharges", inv.RoomCharges1);
                comm.Parameters.AddWithValue("@tax", inv.Tax1);
                comm.Parameters.AddWithValue("@total", inv.Total1);
                comm.Parameters.AddWithValue("@additionalremarks", inv.AdditionalRemarks1);
                rows = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rows;
        }

        public static int CreatePaymentInvoiceDetail(PaymentInvoiceDetail invd)
        {
            int rows = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO PaymentInvoiceDetail(PaymentInvoiceID, RoomID, RoomType, RoomRate, Description)" +
                " VALUES (@paymentinvoiceid, @roomid, @roomtype, @roomrate, @description)";

                comm.Parameters.AddWithValue("@paymentinvoiceid", invd.PaymentInvoiceID1);
                comm.Parameters.AddWithValue("@roomid", invd.RoomID1);
                comm.Parameters.AddWithValue("@roomtype", invd.RoomType1);
                comm.Parameters.AddWithValue("@roomrate", invd.RoomRate1);
                comm.Parameters.AddWithValue("@description", invd.Description1);
                rows = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rows;
        }

        public string generateStaffID()
        {
            string staffID = "";
            int rows = 0;
            SqlConnection conn = null;
            SqlCommand cmd = null;

            string strSQL = "SELECT COUNT(*) FROM UserAccount";

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSDBConnectionString"].ConnectionString;
                conn.Open();

                cmd = new SqlCommand(strSQL, conn);
                rows = (int)cmd.ExecuteScalar();

                rows = rows + 1;
                staffID = rows.ToString();
                if (rows <= 9)
                    staffID = staffID.PadLeft(staffID.Length + 4, '0');
                else if (rows <= 99)
                    staffID.PadLeft(staffID.Length + 3, '0');
                else if (rows <= 999)
                    staffID.PadLeft(staffID.Length + 2, '0');
                else if (rows <= 9999)
                    staffID.PadLeft(staffID.Length + 1, '0');

            }
            catch (SqlException sqlex)
            {
                staffID = sqlex.Message;
            }
            catch (Exception ex)
            {
                staffID = ex.Message;
            }
            finally
            {
                conn.Close();
            }
            return staffID;
        }

        public static int CreateUserAccount(UserAccount a)
        {
            int rows = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO UserAccount (StaffID, Password, StaffType, StaffNRIC)" +
                " VALUES (@staffid, @password, @stafftype, @staffnric)";

                comm.Parameters.AddWithValue("@staffid", a.StaffID1);
                comm.Parameters.AddWithValue("@password", a.Password1);
                comm.Parameters.AddWithValue("@stafftype", a.StaffType1);
                comm.Parameters.AddWithValue("@staffnric", a.StaffNRIC1);

                rows = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                return rows = 2;
            }
            return rows;
        }

        public static UserAccount GetAccountByStaffID(string StaffID)
        {
            SqlConnection conn = null;
            UserAccount ua = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString =
                    ConfigurationManager.ConnectionStrings["HMSDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM UserAccount WHERE StaffID=@staffid";
                comm.Parameters.AddWithValue("@staffid", StaffID);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    ua = new UserAccount();
                    ua.StaffID1 = (string)dr["StaffID"];
                    ua.Password1 = (string)dr["Password"];
                    ua.StaffType1 = (string)dr["StaffType"];
                    ua.StaffNRIC1 = (string)dr["StaffNRIC"];
                }
                dr.Close();

            }
            catch (SqlException e)
            {
                throw e;
            }

            return ua;
        }

        public static UserAccount GetAccountByStaffNRIC(string StaffNRIC)
        {
            SqlConnection conn = null;
            UserAccount ua = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString =
                    ConfigurationManager.ConnectionStrings["HMSDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM UserAccount WHERE StaffNRIC=@staffnric";
                comm.Parameters.AddWithValue("@staffnric", StaffNRIC);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    ua = new UserAccount();
                    ua.StaffID1 = (string)dr["StaffID"];
                    ua.Password1 = (string)dr["Password"];
                    ua.StaffType1 = (string)dr["StaffType"];
                    ua.StaffNRIC1 = (string)dr["StaffNRIC"];
                }
                dr.Close();

            }
            catch (SqlException e)
            {
                throw e;
            }

            return ua;
        }

        public static int UpdateUserAccount(UserAccount a)
        {
            int rowsupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE UserAccount SET StaffID=@staffid, Password=@password, StaffType=@stafftype, StaffNRIC=@staffnric where StaffID=@staffid";
                comm.Parameters.AddWithValue("@staffid", a.StaffID1);
                comm.Parameters.AddWithValue("@password", a.Password1);
                comm.Parameters.AddWithValue("@stafftype", a.StaffType1);
                comm.Parameters.AddWithValue("@staffnric", a.StaffNRIC1);
                rowsupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        }

        public static int DeleteUserAccount(string staffID)
        {
            int rowsdeleted = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString =
                    ConfigurationManager.ConnectionStrings["HMSDBConnectionString"].ConnectionString;
                conn.Open();

                //prepare SQL command
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM UserAccount WHERE StaffID=@staffID";
                comm.Parameters.AddWithValue("@staffID", staffID);
                //Execute the SQL command
                rowsdeleted = comm.ExecuteNonQuery();

                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsdeleted;
        }

        public static int DeleteUserAccount1(string staffNRIC)
        {
            int rowsdeleted = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString =
                    ConfigurationManager.ConnectionStrings["HMSDBConnectionString"].ConnectionString;
                conn.Open();

                //prepare SQL command
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM UserAccount WHERE StaffNRIC=@staffNRIC";
                comm.Parameters.AddWithValue("@staffnric", staffNRIC);
                //Execute the SQL command
                rowsdeleted = comm.ExecuteNonQuery();

                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsdeleted;
        }

        public static string validateLogin1(string staffID, string password, string stafftype)
        {
            string login = "";
            string message;
            SqlConnection conn = null;
            SqlCommand cmd = null;
            SqlDataReader dr = null;

            string strSQL = "SELECT * FROM UserAccount WHERE StaffID=@staffid";

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["HMSDBConnectionString"].ConnectionString;
                conn.Open();



                cmd = new SqlCommand(strSQL, conn);
                cmd.Parameters.AddWithValue("@staffid", staffID);

                dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    string inputStaffID = staffID;
                    string inputPassword = password;
                    string inputStaffType = stafftype;

                    string retrieveStaffID = dr["StaffID"].ToString();
                    string retrievePassword = dr["Password"].ToString();
                    string retrieveStaffType = dr["StaffType"].ToString();

                    if ((inputStaffID.Equals(retrieveStaffID)) && (inputPassword.Equals(retrievePassword)) && (inputStaffType.Equals(retrieveStaffType)))
                    {
                        login = "true";
                    }
                    else
                    {
                        login = "false";
                    }
                    dr.Close();
                }
            }
            catch (SqlException sqlex)
            {
                message = sqlex.Message;
            }
            catch (Exception ex)
            {
                message = ex.Message;
            }
            finally
            {
                conn.Close();
                dr.Close();
            }
            return login;
        }

        public static string check(string StaffNRIC)
        {
            SqlConnection conn = null;
            Staff ua = null;
            string c = "";
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString =
                    ConfigurationManager.ConnectionStrings["HMSDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM STAFF WHERE StaffNRIC=@staffnric";
                comm.Parameters.AddWithValue("@staffnric", StaffNRIC);
                SqlDataReader dr = comm.ExecuteReader();
                ua = new Staff();
                while (dr.Read())
                {           
                    ua.StaffNRIC1 = (string)dr["StaffNRIC"];
                }
                dr.Close();

                if (ua.StaffNRIC1 != null)
                {
                    if (ua.StaffNRIC1 == StaffNRIC)
                    {
                        c = "ok";
                    }
                }

                if (ua.StaffNRIC1 != StaffNRIC)
                {
                    c = "no";
                }
                
            }
            catch (SqlException e)
            {
                throw e;
            }

            return c;
        }

        public static int InsertReport(ReportClass R)
        {
            int rowsupdated = 0;

            //connect to database
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                conn.Open();

                //prepare SQL command to update resident

                SqlCommand comn = new SqlCommand();
                comn.Connection = conn;
                comn.CommandText = "INSERT INTO [hmsdb].[dbo].[Report] (ReportID, ReportTitle, StaffID, ReportType, Date)VALUES(@ReportID, @ReportTitle, @StaffID, @ReportType, @Date)";

                //pass data into SQL command
                comn.Parameters.AddWithValue("@ReportID", R.reportID);
                comn.Parameters.AddWithValue("@ReportTitle", R.reportTitle);
                comn.Parameters.AddWithValue("@StaffID", R.staffID);
                comn.Parameters.AddWithValue("@ReportType", R.reportType);
                comn.Parameters.AddWithValue("@Date", R.date);


                //execute the command

                rowsupdated = comn.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }

            conn.Close();

            return rowsupdated;

        }

        public static ArrayList GetReportByReportID(int reportID)
        {
            ArrayList array = new ArrayList();
            SqlConnection con = null;
            try
            {
                //1.Establish DB connection
                con = new SqlConnection();
                con.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open(); // to connect to DB


                //2.Prepare SQL statement and provide parameter
                SqlCommand comm = new SqlCommand();
                comm.Connection = con;
                comm.CommandText = "SELECT *  FROM [hmsdb].[dbo].[Report] WHERE ReportID = @ReportID";
                comm.Parameters.AddWithValue("@ReportID", reportID);


                //3.Execute SQL statement and read the data
                SqlDataReader dr = comm.ExecuteReader();


                //4.While data is read , put it into the music object
                if (dr.Read())
                {
                    //create music object
                    ReportClass r = new ReportClass();

                    //store DB sata into the resident object
                    r.reportID = (String)dr["ReportID"];
                    r.reportTitle = (String)dr["ReportTitle"];
                    r.staffID = Convert.ToInt32(dr["StaffID"]);
                    r.reportType = (String)dr["ReportType"];
                    r.date = Convert.ToDateTime(dr["Date"]);
                    array.Add(r);


                }

                dr.Close();
            }


            catch (SqlException e)
            {
                throw e;
            }

            return array;
        }

        public static int UpdateReport(ReportClass R)
        {
            int rowsupdated = 0;

            //connect to database
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                conn.Open();

                //prepare SQL command to update music

                SqlCommand comn = new SqlCommand();
                comn.Connection = conn;
                comn.CommandText = "UPDATE [hmsdb].[dbo].[Report] SET ReportID=@ReportID, ReportTitle=@ReportTitle, StaffID=@StaffID, ReportType=@ReportType, Date=@Date WHERE ReportID=@ReportID ";

                //pass data into SQL command
                comn.Parameters.AddWithValue("@ReportID", R.reportID);
                comn.Parameters.AddWithValue("@ReportTitle", R.reportTitle);
                comn.Parameters.AddWithValue("@StaffID", R.staffID);
                comn.Parameters.AddWithValue("@ReportType", R.reportType);
                comn.Parameters.AddWithValue("@Date", R.date);


                //execute the command

                rowsupdated = comn.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }

            return rowsupdated;
        }

        public static int DeleteReport(int reportID)
        {
            int rowsDeleted = 0;
            //connect to database
            SqlConnection conn = null;
            try
            {
                //connect to database
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                conn.Open();
                //prepare SQl command to update music
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE * FROM [hmsdb].[dbo].[Report] WHERE ReportID=@ReportID ";

                //pass data into the SQL Command
                comm.Parameters.AddWithValue("@ReportID", reportID);

                //execute the command
                rowsDeleted = comm.ExecuteNonQuery();

                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsDeleted;
        }

        public static Room GetRoomRateByRoomType(string roomType)
        {
            SqlConnection conn = null;
            Room r = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString =
                    ConfigurationManager.ConnectionStrings["HMSDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT TOP (1) * FROM ROOM WHERE RoomType=@roomtype";
                comm.Parameters.AddWithValue("@roomtype", roomType);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    r = new Room();
                    r.RoomRate1 = (decimal)dr["RoomRate"];

                }
                dr.Close();

            }
            catch (SqlException e)
            {
                throw e;
            }

            return r;
        }

    }
}